﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using gp.BiztalkHelpers.PipelinesReferenceViewer.Model;

namespace gp.BiztalkHelpers.PipelinesReferenceViewer.DAL
{
	public class DAO
	{
		private string _connectionString;
		
		#region Singleton
		private DAO(string connectionString) {
			this._connectionString = connectionString;
		}

		private static DAO Instance;

		public static DAO GetInstance(string connectionString) {
			if (Instance == null) {
				Instance = new DAO(connectionString);
			}

			return Instance;
		}
		#endregion

		#region Queries Pipelines
		/// <summary>
		/// Get all pipelines in Managment DB
		/// </summary>
		/// <param name="type">Category of pipeline</param>
		/// <returns>A list of all pipelines</returns>
		public IEnumerable<Pipeline> GetAllPipelines(PipelineCategory categ) {
			var result = new List<Pipeline>();

			using (var conn = new SqlConnection(this._connectionString)) {
				conn.Open();

				using (var cmd = new SqlCommand("select * from bts_pipeline where Category = " + (int)categ, conn)) {
					var reader = cmd.ExecuteReader();

					if (reader.HasRows) {
						while (reader.Read()) {
							var pipeline = new Pipeline();

							pipeline.Id = reader.GetInt32(0);
							pipeline.PipelineId = reader.GetGuid(1);
							pipeline.Category = reader.GetInt16(2);
							pipeline.Name = reader.GetString(3);
							pipeline.FullyQualifiedName = reader.GetString(4);
							pipeline.IsStreaming = reader.GetInt16(5);
							pipeline.AssemblyID = reader.GetInt32(6);

							if (!reader.IsDBNull(7)) {
								pipeline.Description = reader.GetString(7);
							}

							pipeline.Release = reader.GetInt32(8);

							result.Add(pipeline);
						}
					}
				}
			}

			return result;
		}
		#endregion

		#region Queries ReceiveLocation
		/// <summary>
		/// Get all the receive locations
		/// </summary>
		/// <returns>List of receive locations</returns>
		public IEnumerable<ReceiveLocation> GetAllReceiveLocations() {
			var result = new List<ReceiveLocation>();

			using (var conn = new SqlConnection(this._connectionString))
			{
				conn.Open();

				var rq = @"
					select 
						rcvl.Id,
						rcvl.Name,
						rcvl.ReceivePipelineId,
						rcvl.SendPipelineId,
						app.nvcName
					from adm_ReceiveLocation rcvl
					inner join bts_receiveport rcvp on
						rcvp.nID = rcvl.ReceivePortId
					inner join bts_application app on
						app.nID = rcvp.nApplicationID
				";

				using (var cmd = new SqlCommand(rq, conn)) {
					var reader = cmd.ExecuteReader();

					if (reader.HasRows) {
						while (reader.Read()) {
							var rcvl = new ReceiveLocation();

							rcvl.Id = reader.GetInt32(0);
							rcvl.Name = reader.GetString(1);
							rcvl.ReceivePipelineId = reader.GetInt32(2);

							if (!reader.IsDBNull(3))
							{
								rcvl.SendPipelineId = reader.GetInt32(3);
							}

							rcvl.ApplicationName = reader.GetString(4);

							result.Add(rcvl);
						}
					}
				}
			}

			return result;
		}

		/// <summary>
		/// Get all the receive locations who uses the specified receive pipeline
		/// </summary>
		/// <param name="rcvPipelineId">Receive Pipeline ID</param>
		/// <returns>List of ReceiveLocations</returns>
		public IEnumerable<ReceiveLocation> GetAllRcvLocationsFromRcvPipeline(int rcvPipelineId) {
			return this.GetAllReceiveLocations().Where(rl => rl.ReceivePipelineId == rcvPipelineId);
		}

		/// <summary>
		/// Get all the receive locations who uses the spedified send pipeline
		/// </summary>
		/// <param name="sndPipelineId">Send Pipeline ID</param>
		/// <returns>List of ReceiveLocations</returns>
		public IEnumerable<ReceiveLocation> GetAllRcvLocationsFromSndPipeline(int sndPipelineId) {
			return this.GetAllReceiveLocations().Where(rl => rl.SendPipelineId.HasValue && rl.SendPipelineId.Value == sndPipelineId);
		}
		#endregion

		#region SendPort
		/// <summary>
		/// Get all the send ports
		/// </summary>
		/// <returns>A list of send ports</returns>
		public IEnumerable<SendPort> GetAllSendPorts() {
			var result = new List<SendPort>();

			using (var conn = new SqlConnection(this._connectionString))
			{
				conn.Open();

				var rq = @"
					select 
						sndp.nID,
						sndp.nvcName,
						sndp.nSendPipelineID,
						sndp.nReceivePipelineID,
						app.nvcName
					from bts_sendport sndp
					inner join bts_application app on 
						app.nID = sndp.nApplicationID 
				";

				using (var cmd = new SqlCommand(rq, conn)) {
					var reader = cmd.ExecuteReader();

					if (reader.HasRows) {
						while (reader.Read()) {
							var sndPort = new SendPort();

							sndPort.Id = reader.GetInt32(0);
							sndPort.Name = reader.GetString(1);
							sndPort.SendPipelineID = reader.GetInt32(2);

							if (!reader.IsDBNull(3)) {
								sndPort.ReceivePipelineID = reader.GetInt32(3);
							}

							sndPort.ApplicationName = reader.GetString(4);

							result.Add(sndPort);
						}
					}
				}
			}

			return result;
		}

		/// <summary>
		/// Get all the send ports who uses the specified send pipeline
		/// </summary>
		/// <param name="sndPipelineId">Send Pipeline ID</param>
		/// <returns>A list of Send Ports</returns>
		public IEnumerable<SendPort> GetAllSndPortsFromSndPipeline(int sndPipelineId) {
			return this.GetAllSendPorts().Where(s => s.SendPipelineID == sndPipelineId);
		}

		/// <summary>
		/// Get all the send ports who uses the specified receive pipeline
		/// </summary>
		/// <param name="rcvPipelineId">Receive Pipeline ID</param>
		/// <returns>A list of Send Ports</returns>
		public IEnumerable<SendPort> GetAllSndPortsFromRcvPipeline(int rcvPipelineId) {
			return this.GetAllSendPorts().Where(s => s.ReceivePipelineID.HasValue && s.ReceivePipelineID.Value == rcvPipelineId);
		}
		#endregion
	}
}
